﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using WL.Model;

namespace WL.Dal
{
    class DataDal
    {
       // private static string tb = "wl_product";



        internal static void saveByDT(DataTable dt, Table t)
        {
            foreach (DataRow row in dt.Rows)
            {
                //System.Windows.Forms.MessageBox.Show(t.fields.Count + "");// ; t.fields.Count;
                string values = "NULL,";
                for (int i = 0; i < t.fields.Count; i++)
                {

                        values += "'"+ row[i].ToString() + "',";
                    
                }
                values = values.Substring(0, values.Length-1);

                string sql = "insert into data_"+t.name+" values("+values+")";
                SQLiteM.ExecuteNonQuery(CommandType.Text, sql);
               
            }
        }
        public static void saveByDT2(DataTable dt, Table t)
        {

            //foreach (DataRow row in dt.Rows)
            //{
            //    //System.Windows.Forms.MessageBox.Show(t.fields.Count + "");// ; t.fields.Count;
            //    string values = "NULL,";
            //    for (int i = 0; i < t.fields.Count; i++)
            //    {

            //        values += "'" + row[i].ToString() + "',";

            //    }
            //    values = values.Substring(0, values.Length - 1);

            //    string sql = "insert into data_" + t.name + " values(" + values + ")";
            //    SQLiteM.ExecuteNonQuery(CommandType.Text, sql);
            //}

            

            DbProviderFactory factory = SQLiteFactory.Instance;
            using (DbConnection conn = factory.CreateConnection())
            {
                //connection db
                conn.ConnectionString = "Data Source=test.db";
                conn.Open();
                DbTransaction trans = conn.BeginTransaction();

                int j = 0;

              
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow row = dt.Rows[i];
                    DbCommand cmd = conn.CreateCommand();
                    string wenhao = "NULL"; //参数？个数
                    for (int count = 1; count <= t.fields.Count; count++)
                    {
                        wenhao += ",?";
                        cmd.Parameters.Add(cmd.CreateParameter());
                    }
                    cmd.Parameters.Add(cmd.CreateParameter());

                    cmd.CommandText = "insert into data_" + t.name + " values(" + wenhao + ")";
                    try
                    {
                        for (int index = 0; index <= t.fields.Count; index++)
                        {
                            cmd.Parameters[index].Value = row[index];
                        }
                        
                        j++;
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        System.Windows.Forms.MessageBox.Show(e.Message);
                    }
                }
                trans.Commit();
            }

            
        }


        internal static DataTable getDatas(string table)
        {
            string sql = "select * from data_" + table;
            return SQLiteM.GetDataSet(CommandType.Text, sql, null).Tables[0];
        }

        internal static DataTable getDatas(Table t, string key)
        {
            string sql = "select * from wl_field where search=1 and pid="+t.id;
            DataTable dt = SQLiteM.GetDataSet(CommandType.Text, sql).Tables[0];

            string where = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (i == 0)
                {
                    where += " where (" + dt.Rows[i]["name"] + " like '%" + key + "%')";
                }
                else
                {
                    where += " or (" + dt.Rows[i]["name"] + " like '%" + key + "%')";
                }

                //where += "and " + dt.Rows[i]["name"] + " = '" + key + "'";
            }
            //System.Windows.Forms.MessageBox.Show(where);

            sql = "select * from data_" + t.name+where;
            //System.Windows.Forms.MessageBox.Show(sql);
            return SQLiteM.GetDataSet(CommandType.Text, sql, null).Tables[0];
        }


        //-------------------------屏蔽以下

        //public static void save(DataRow row)
        //{
        //    string sql = string .Format("insert into {0} values(NULL,'{1}',{2},{3},'{4}',{5},'')",
        //                                tb,row[0],row[1],row[2],row[3],row[4]);
        //    //System.Windows.Forms.MessageBox.Show(sql);
        //    SQLiteM.ExecuteInsert(CommandType.Text, sql);
        //}
        //public static DataTable getAll() {
        //    return SQLiteM.GetDataSet(CommandType.Text, "select * from "+tb, null).Tables[0];
        //}

        //public static List<int> save(DataTable dt, string upfile, Table t)
        //{
        //    List<int> indexs = new List<int>();//保存出错的列

        //    DbProviderFactory factory = SQLiteFactory.Instance;
        //    using (DbConnection conn = factory.CreateConnection())
        //    {
        //        //connection db
        //        conn.ConnectionString = "Data Source=test.db";
        //        conn.Open();
        //        DbTransaction trans = conn.BeginTransaction();

        //        int j = 0;
        //        for (int i = 0; i < dt.Rows.Count; i++)
        //        {
        //            DataRow row = dt.Rows[i];
        //            DbCommand cmd = conn.CreateCommand();
        //            string wenhao = "?"; //参数？个数
        //            for (int count = 1; count <= t.fields.Count; count++)
        //            {
        //                wenhao += ",?";
        //                cmd.Parameters.Add(cmd.CreateParameter());
        //            }
        //            cmd.Parameters.Add(cmd.CreateParameter());

        //            cmd.CommandText = "insert into " + t.name + " values(" + wenhao + ")";
        //            try
        //            {
        //                for(int index=0; index<=t.fields.Count;index++) {
        //                    cmd.Parameters[index].Value = row[index];
        //                }
        //                //此处bug，对于没有数据，为空的值，我们会报错，暂时不管，在这里加异常处理，添加不成功，记录下来
        //                //cmd.Parameters[1].Value = Convert.ToInt32(row[1].ToString().Replace(",", ""));
        //                //cmd.Parameters[2].Value = Convert.ToInt32(row[2].ToString().Replace(",", ""));
        //                //cmd.Parameters[3].Value = row[3];
        //                //cmd.Parameters[4].Value = Convert.ToDecimal(row[4].ToString().Replace(",", ""));
        //                //cmd.Parameters[5].Value = upfile;
        //                //cmd.Parameters[6].Value = Convert.ToInt32(row[5].ToString());

        //                j++;
        //                cmd.ExecuteNonQuery();
        //            }
        //            catch (Exception e)
        //            {
        //                indexs.Add(i);
        //            }
        //        }
        //        trans.Commit();
        //    }

        //    return indexs;
        //}

        //public static List<int> save(DataTable dt, string upfile)
        //{
        //    List<int> indexs = new List<int>();//保存出错的列

        //    DbProviderFactory factory = SQLiteFactory.Instance;
        //    using (DbConnection conn = factory.CreateConnection())
        //    {
        //        // 连接数据库  
        //        conn.ConnectionString = "Data Source=test.db";
        //        conn.Open();
        //        DbTransaction trans = conn.BeginTransaction();

        //        int j = 0;
        //        //string ss = "";
                

        //        for (int i = 0; i < dt.Rows.Count; i++)
        //        {
        //            DataRow row = dt.Rows[i];
        //            DbCommand cmd = conn.CreateCommand();
        //            for (int count = 1; count <= 7; count++)
        //            {
        //                cmd.Parameters.Add(cmd.CreateParameter());
        //            }
                    

        //            cmd.CommandText = string.Format("insert into {0} values(NULL,?,?,?,?,?,'',?,?)",
        //                                            tb);
        //            try
        //            {
        //                cmd.Parameters[0].Value = row[0];
        //                //此处bug，对于没有数据，为空的值，我们会报错，暂时不管，在这里加异常处理，添加不成功，记录下来
        //                cmd.Parameters[1].Value = Convert.ToInt32(row[1].ToString().Replace(",", ""));
        //                cmd.Parameters[2].Value = Convert.ToInt32(row[2].ToString().Replace(",", ""));
        //                cmd.Parameters[3].Value = row[3];
        //                cmd.Parameters[4].Value = Convert.ToDecimal(row[4].ToString().Replace(",", ""));
        //                cmd.Parameters[5].Value = upfile;
        //                cmd.Parameters[6].Value = Convert.ToInt32(row[5].ToString());

        //                j++;
        //                cmd.ExecuteNonQuery();
        //            } 
        //            catch (Exception e)
        //            {
        //                indexs.Add(i);
        //            }
        //        }

        //        trans.Commit();
                
        //        //catch (Exception e)
        //        //{
        //        //    System.Windows.Forms.MessageBox.Show(j+"");
        //        //    System.Windows.Forms.MessageBox.Show(ss);
        //        //    System.Windows.Forms.MessageBox.Show(e.Message);
        //        //    trans.Rollback();
        //        //}
 
        //    }
        //    return indexs;
                      
        //}

        //  //internal static void FastInsertMany(DbConnection cnn)
        //  //  {
        //  //  using (DbTransaction dbTrans = cnn.BeginTransaction())
        //  //  {
        //  //  using (DbCommand cmd = cnn.CreateCommand())
        //  //  {
        //  //      try
        //  //     {
        //  //  cmd.CommandText = "INSERT INTO TestCase(MyValue) VALUES(?)";
        //  //  DbParameter Field1 = cmd.CreateParameter();
        //  //  cmd.Parameters.Add(Field1);
        //  //  for (int n = 0; n < 100000; n++)
        //  //  {
        //  //  Field1.Value = n + 100000;
        //  //  cmd.ExecuteNonQuery();
        //  //  }
        //  //  }
        //  //    dbTrans.Commit();
        //  //     }
        //  //     catch
        //  //     {
        //  //       dbTrans.RollBack();
        //  //     }
        //  //  }
        //  //  }
        //internal static int update(Product p)
        //{
        //   // string sql = "update "+tb+" set no=?,freeStock=?,packQty=?,manufacturer=?,price=?,remark=? where id="+p.id;
        //    string sql = string.Format( "update " + tb + " set no='{0}',freeStock={1},packQty={2},manufacturer='{3}',price={4},remark='{5}',upfile='{6}',line={7} where id=" + p.id,
        //                                p.no, p.freeStock, p.packQty, p.manufacturer,p.price,p.remark, p.upfile,p.line );

        //    return SQLiteM.ExecuteNonQuery(CommandType.Text, sql);
        //    //return false;
        //} 

        ///// <summary>
        /////根据条件获取数据
        ///// </summary>
        ///// <param name="key">关键字（供应商、型号）</param>
        ///// <param name="upfile"></param>
        ///// <returns></returns>
        //internal static DataTable getDatas(string key, string upfile)
        //{
        //    string where = " where 1=1";
        //    if (key != null)
        //    {
        //        where += " and (no like '%" + key + "%' or manufacturer like '%"+key+"%')";
        //    }
        //    if (upfile != null)
        //    {
        //        where += " and upfile='"+upfile+"'";
        //    }

        //    string sql = "select * from " + tb + where;
        //    return SQLiteM.GetDataSet(CommandType.Text, sql, null).Tables[0];
        //}


        ///// <summary>
        ///// 根据id删除
        ///// </summary>
        ///// <param name="iid"></param>
        ///// <returns></returns>
        //internal static int delete(int iid)
        //{
        //    string sql = "delete from " + tb + " where id="+iid;// like '%" + no + "%'";

        //    return SQLiteM.ExecuteNonQuery(CommandType.Text,sql);
        //}
        ///// <summary>
        ///// 获取数据的文件名列表
        ///// </summary>
        ///// <returns></returns>
        //internal static string[] getFileNames()
        //{
        //    string sql = "select distinct upfile from wl_product";
        //    DataTable ds = SQLiteM.GetDataSet(CommandType.Text, sql).Tables[0];

        //    List<string> l = new List<string>();

        //    foreach (DataRow row in ds.Rows) {
        //        l.Add(row[0].ToString());
        //    }
        //    return l.ToArray();
        //}

        ///// <summary>
        ///// 根据文件名删除数据
        ///// </summary>
        ///// <param name="upfile"></param>
        //internal static void deleteByUpfile(string upfile)
        //{
        //    string sql = "delete from " + tb + " where upfile=?";// like '%" + no + "%'";
        //    SQLiteParameter[] param= SQLiteM.GetParams(new string[] { "upfile" }, new object[] {upfile});

        //    SQLiteM.ExecuteNonQuery(CommandType.Text, sql, param);
        //}




        internal static DataTable getDatas(string table, string key)
        {
            throw new NotImplementedException();
        }

        internal static int deleteById(Table t , int id)
        {
            string sql = "delete from data_"+t.name + " where id="+id;
            return SQLiteM.ExecuteNonQuery(CommandType.Text, sql);
        }

        internal static int update(Table t, int dataid, string values)
        {
            string[] varr = values.Split(',');
            string sql = string.Format("update data_{0} set {1} where id={2}", t.name,values, dataid );// 
            return SQLiteM.ExecuteNonQuery(CommandType.Text, sql);
        }
    }
}
